﻿using System;
using System.Data;
using System.Web;
using System.Web.UI;
using Telerik.Web.UI;
using KPIS.GERP.GBMS.IBLL;
using KPIS.GERP.GBMS.BLL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.DBM;
using System.IO;

namespace KPIS.GERP.GBMS.APP_UI
{
    public partial class BGM_Request : System.Web.UI.Page
    {
        private ComboBLL comboBLL = new ComboBLL();
        private SystemControlBLL scBLL = new SystemControlBLL();
        private ISourceBLL src = new SourceBLL();
        private IProjectControl projectBLL;
        private IProject projectBLL2;
        string type;
        int year = SystemInfo._CurrentYearReq;
        private SsMng ssMng = new SsMng();

        protected void Page_Load(object sender, EventArgs e)
        {
            HttpCookie cookiex = Request.Cookies["UserDetail"];

            if (cookiex != null && Session["EmpSeq"] == null)
            {
                ssMng.SetLoginSession(cookiex["UserSeq"], cookiex["EmpSeq"], cookiex["UserName"]
                                      , cookiex["WorkingJobSiteSeq"], cookiex["Height"]);
            }
            type = Request.QueryString["type"].ToString();

            if (type.Length > 0)
            {
                projectBLL = Project_Factory.GetProjectControlFactory(type);
                projectBLL2 = Project_Factory.GetProjectFactory(type);
            }

            if (!IsPostBack)
            {
                LoadYear();
                LoadJobSiteCombo();
                LoadBgmSourceCombo();
                LoadData();
            }
        }

        private void LoadYear()
        {
            int CurrYear = SystemInfo._CurrentYearReq;
            ddlYear.Items.Clear();

            for (int i = CurrYear + 5; i >= CurrYear - 5; i--)
            {
                ddlYear.Items.Add(new RadComboBoxItem(i.ToString(), i.ToString()));
            }
            ddlYear.SelectedValue = CurrYear.ToString();
        }
        private void LoadJobSiteCombo()
        {
            string jobSiteID = new SsMng().WorkingJobSiteSeq;
            DataTable dt = comboBLL.ListJobsite();
            ddlJobSite.DataSource = dt;
            ddlJobSite.DataBind();
            ddlJobSite.Items.Insert(0, new RadComboBoxItem("----- หน่วยงานทั้งหมด -----", ""));

            var item = ddlJobSite.Items.FindItemByValue(jobSiteID);
            ddlJobSite.SelectedIndex = item != null ? item.Index : 0;


            if (ddlJobSite.FindItemByValue(jobSiteID) != null) // มีหน่วยงานของผู้ Login
                ddlJobSite.SelectedValue = jobSiteID;
            else // ไม่มีหน่วยงานของผู้ล็อกอิน เกิดจากผู้ล็อกอิน อยู่ในสังกัด ที่มีระดับสูงกว่าสำนัก เช่น กรม
                ddlJobSite.SelectedIndex = 0;

            if (jobSiteID != scBLL.JobSiteStrategyID() && jobSiteID != "127" && jobSiteID != "")// ยุทธศาสตร์=1300913028       แผน=1300912975
            {
                ddlJobSite.Enabled = false;
                //btnExportExcel.Visible = false;
                //ddlYear.AutoPostBack = true;
            }
            else
            {
                ddlJobSite.Enabled = true;
                //btnExportExcel.Visible = true;
                //ddlYear.AutoPostBack = false;
            }
        }
        private void LoadBgmSourceCombo()
        {
            cbSource.DataSource = comboBLL.ListSourceCombo();
            cbSource.DataBind();

            cbSource.Items.Insert(0, new RadComboBoxItem("----- แหล่งเงินทั้งหมด -----", ""));
            cbSource.SelectedIndex = 0;
        }

        private void LoadData()
        {
            LoadGridProject();
            LoadGridProjectDtls();
            LoadGridSource();
            LoadGridBgmExpGrp();
        }

        private void LoadGridProject()
        {
            year = int.Parse(ddlYear.SelectedValue);
            string strRecordStatus = ddlRecordStatus.SelectedValue;

            if (strRecordStatus == "W")
            {
                strRecordStatus = "";
            }

            gridProject.DataSource = projectBLL.GetDataStrategyControl(year, ddlJobSite.SelectedValue, false, strRecordStatus);
            gridProject.DataBind();
        }

        private void LoadGridProjectDtls()
        {
            string strRecordStatus = ddlRecordStatus.SelectedValue;
            string strStratSeq = "";

            if (strRecordStatus == "W")
            {
                strRecordStatus = "";
            }

            if (gridProject.SelectedItems.Count > 0)
            {
                strStratSeq = ((GridDataItem)gridProject.SelectedItems[0])["bgm_strat_list_seq"].Text;
            }

            gridProjectDtls.DataSource = projectBLL2.GetDataProjectByYearAndJobSiteAndSubAct(ddlYear.SelectedValue, ddlJobSite.SelectedValue
                , strStratSeq, strRecordStatus);
            gridProjectDtls.DataBind();
        }

        private void LoadGridSource()
        {
            year = int.Parse(ddlYear.SelectedValue);
            string strRecordStatus = ddlRecordStatus.SelectedValue;
            string strStratSeq = "";
            string strBgmProjSeq = "";

            if (strRecordStatus == "W")
            {
                strRecordStatus = "";
            }

            if (gridProject.SelectedItems.Count > 0)
            {
                strStratSeq = ((GridDataItem)gridProject.SelectedItems[0])["bgm_strat_list_seq"].Text;
            }

            if (gridProjectDtls.SelectedItems.Count > 0)
            {
                strBgmProjSeq = ((GridDataItem)gridProjectDtls.SelectedItems[0]).GetDataKeyValue("Sequence").ToString();
                gridSource.DataSource = projectBLL2.GetDataProjectBySequenceForGridIn(strBgmProjSeq);
                gridSource.DataBind();
            }
            else
            {
                gridSource.DataSource = projectBLL.GetDataProjectBySequenceForGridIn(year.ToString(), ddlJobSite.SelectedValue, strStratSeq, strRecordStatus);
                gridSource.DataBind();
            }
        }

        private void LoadGridBgmExpGrp()
        {
            year = int.Parse(ddlYear.SelectedValue);
            string strRecordStatus = ddlRecordStatus.SelectedValue;
            string strStratSeq = "";
            string strBgmProjSeq = "";

            if (strRecordStatus == "W")
            {
                strRecordStatus = "";
            }

            if (gridProject.SelectedItems.Count > 0)
            {
                strStratSeq = ((GridDataItem)gridProject.SelectedItems[0])["bgm_strat_list_seq"].Text;
            }

            if (gridProjectDtls.SelectedItems.Count > 0)
            {
                strBgmProjSeq = ((GridDataItem)gridProjectDtls.SelectedItems[0]).GetDataKeyValue("Sequence").ToString();
                gridBgmExpGrp.DataSource = projectBLL2.GetDataProjectExpGrpBySequenceForGridIn(strBgmProjSeq);
                gridBgmExpGrp.DataBind();
            }
            else
            {
                gridBgmExpGrp.DataSource = projectBLL.GetDataProjectExpGrpBySequenceForGridIn(year.ToString(), ddlJobSite.SelectedValue, strStratSeq, strRecordStatus);
                gridBgmExpGrp.DataBind();
            }
        }

        protected void ddlJobSite_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            //if(ddlJobSite.SelectedValue.Length > 0)
            LoadData();
        }

        protected void ddlRecordStatus_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            //if(ddlJobSite.SelectedValue.Length > 0)
            LoadData();
        }

        protected void gridSource_ItemDataBound(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridFooterItem)
            {
                GridFooterItem footer = (GridFooterItem)e.Item;
                footer["bgm_source_name"].Controls.Add(new LiteralControl("<span style='color: Black; font-weight: bold;'>รวมทั้งหมด :</span> "));
            }
        }

        protected void gridProject_ItemDataBound(object sender, GridItemEventArgs e)
        {

            if (e.Item is GridFooterItem)
            {
                GridFooterItem footer = (GridFooterItem)e.Item;
                footer["name"].Controls.Add(new LiteralControl("<span style='color: Black; font-weight: bold;'>รวมทั้งหมด :</span> "));
            }
        }

        protected void gridProjectDtls_ItemDataBound(object sender, GridItemEventArgs e)
        {

            if (e.Item is GridFooterItem)
            {
                GridFooterItem footer = (GridFooterItem)e.Item;
                footer["bgm_prj_name"].Controls.Add(new LiteralControl("<span style='color: Black; font-weight: bold;'>รวมทั้งหมด :</span> "));
            }
        }

        protected void gridProjectByOption_ItemDataBound(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridFooterItem)
            {
                GridFooterItem footer = (GridFooterItem)e.Item;
                footer["name"].Controls.Add(new LiteralControl("<span style='color: Black; font-weight: bold;'>รวมทั้งหมด :</span> "));
            }
        }

        protected void gridMarket_ItemDataBound(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridFooterItem)
            {
                GridFooterItem footer = (GridFooterItem)e.Item;
                footer["bgm_option_name"].Controls.Add(new LiteralControl("<span style='color: Black; font-weight: bold;'>รวมทั้งหมด :</span> "));
            }
        }

        protected void gridProduct_ItemDataBound(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridFooterItem)
            {
                GridFooterItem footer = (GridFooterItem)e.Item;
                footer["bgm_option_name"].Controls.Add(new LiteralControl("<span style='color: Black; font-weight: bold;'>รวมทั้งหมด :</span> "));
            }
        }

        protected void gridBgmExpGrp_ItemDataBound(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridFooterItem)
            {
                GridFooterItem footer = (GridFooterItem)e.Item;
                footer["bgm_exp_grp_name"].Controls.Add(new LiteralControl("<span style='color: Black; font-weight: bold;'>รวมทั้งหมด :</span> "));
            }
        }

        protected void gridProject_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadGridProjectDtls();
            LoadGridSource();
            LoadGridBgmExpGrp();
        }

        protected void gridProjectDtls_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadGridSource();
            LoadGridBgmExpGrp();
        }

        protected void ddlYear_SelectedIndexChanged(object o, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            if(ddlJobSite.Text.Length > 0)
                LoadData();
        }

        protected void btnExportExcel_Click(object sender, EventArgs e)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            DataTable ResultDTB = new DataTable();
            DataTable dtb;
            string strRecordStatus = ddlRecordStatus.SelectedValue;
            string strOldBgmExpGrpSeq = "";
            string strOldBgmExpName = "";

            if (strRecordStatus == "W")
            {
                strRecordStatus = "";
            }

            dbManager.Open();

            try
            {
                string strSQL = "";

                if (rblExpenseLevel.SelectedValue == "2")
                {
                    strSQL = "select distinct e2.bgm_exp_seq, e2.bgm_exp_code"
                        + ", concat(ge.bgm_exp_grp_name, '/', e2.bgm_exp_name) as bgm_exp_name"//, '/', e1.bgm_exp_name) as bgm_exp_name"
                        + ", e2.bgm_exp_grp_seq, ge.bgm_exp_grp_name"
                        + " from bgm_request r"
                        + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                        + (cbSource.SelectedValue.Length > 0 ? " and re.bgm_source_seq = " + cbSource.SelectedValue : "")
                        + " inner join bgm_mas_expense e1 on re.bgm_exp_seq = e1.bgm_exp_seq"// and e1.record_status not like 'D'"
                        + " inner join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"// and e2.record_status not like 'D'"
                        + " inner join bgm_mas_expense_group ge on e2.bgm_exp_grp_seq = ge.bgm_exp_grp_seq"

                        + " where 0=0"
                        + (strRecordStatus.Length > 0 ?
                                (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                                : " and r.record_status not like 'D'")
                            + " and r.bgm_year = " + ddlYear.SelectedValue
                            + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                        + " order by e2.bgm_exp_grp_seq, e2.bgm_exp_code";
                }
                else
                {
                    strSQL = "select distinct e1.bgm_exp_seq, e1.bgm_exp_code"
                        + ", concat(ge.bgm_exp_grp_name, '/', e1.bgm_exp_name) as bgm_exp_name"//, '/', e1.bgm_exp_name) as bgm_exp_name"
                        + ", e1.bgm_exp_grp_seq, ge.bgm_exp_grp_name"
                        + " from bgm_request r"
                        + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                        + (cbSource.SelectedValue.Length > 0 ? " and re.bgm_source_seq = " + cbSource.SelectedValue : "")
                        + " inner join bgm_mas_expense e1 on re.bgm_exp_seq = e1.bgm_exp_seq"// and e1.record_status not like 'D'"
                        //+ " inner join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"// and e2.record_status not like 'D'"
                        + " inner join bgm_mas_expense_group ge on e1.bgm_exp_grp_seq = ge.bgm_exp_grp_seq"

                        + " where 0=0"
                        + (strRecordStatus.Length > 0 ?
                                (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                                : " and r.record_status not like 'D'")
                            + " and r.bgm_year = " + ddlYear.SelectedValue
                            + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                        + " order by e1.bgm_exp_grp_seq, e1.bgm_exp_code";
                }
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                ResultDTB.Columns.Add("กิจกรรมย่อย");

                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    string strBgmExpGrpSeq = dtb.Rows[i]["bgm_exp_grp_seq"].ToString();
                    string strBgmExpName1 = dtb.Rows[i]["bgm_exp_name"].ToString();

                    if (strOldBgmExpGrpSeq != strBgmExpGrpSeq)
                    {
                        ResultDTB.Columns.Add(dtb.Rows[i]["bgm_exp_grp_name"].ToString());
                        strOldBgmExpGrpSeq = strBgmExpGrpSeq;
                    }

                    if (strOldBgmExpName != strBgmExpName1)
                    {
                        ResultDTB.Columns.Add(dtb.Rows[i]["bgm_exp_name"].ToString());
                        strOldBgmExpName = strBgmExpName1;
                    }
                }

                ResultDTB.Columns.Add("รวมทั้งหมด");

                if (rblExpenseLevel.SelectedValue == "2")
                {
                    strSQL = "select t.bgm_strat_list_code, t.bgm_strat_list_name"
                        + ", t.bgm_exp_seq, t.bgm_exp_code, t.bgm_exp_name"
                        + ", t.bgm_exp_grp_seq, t.bgm_exp_grp_name"
                        + ", sum(coalesce(t.total_amount, 0)) as total_amount"

                        + " from ("
                            + " select sdl.bgm_strat_list_code, sdl.bgm_strat_list_name"
                            + ", e.bgm_exp_seq, e.bgm_exp_code"
                            + ", concat(ge.bgm_exp_grp_name, '/', e.bgm_exp_name) as bgm_exp_name"//, '/', e1.bgm_exp_name) as bgm_exp_name"
                            + ", e1.bgm_exp_grp_seq, ge.bgm_exp_grp_name"
                            + ", re.total_amount"
                            + " from bgm_request r"
                            + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                            + " inner join bgm_mas_expense e1 on re.bgm_exp_seq = e1.bgm_exp_seq"
                            + " inner join bgm_mas_expense e on e1.upper_bgm_exp_seq = e.bgm_exp_seq"
                            + " inner join bgm_mas_expense_group ge on e1.bgm_exp_grp_seq = ge.bgm_exp_grp_seq"
                            + " inner join bgm_strategy_data_list sdl on r.bgm_strat_list_seq = sdl.bgm_strat_list_seq"
                            + " where 0 = 0"
                                + (strRecordStatus.Length > 0 ?
                                    (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                                    : " and r.record_status not like 'D'")
                            + " and r.bgm_year = " + ddlYear.SelectedValue
                            + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                            + (cbSource.SelectedValue.Length > 0 ? " and re.bgm_source_seq = " + cbSource.SelectedValue : "")
                        //+ " and re.bgm_source_seq = " + cbSource.SelectedValue
                        + ") t"
                        + " group by t.bgm_strat_list_code, t.bgm_strat_list_name , t.bgm_exp_grp_name"
                            + ", t.bgm_exp_seq, t.bgm_exp_code, t.bgm_exp_name, t.bgm_exp_grp_seq"
                             + ", t.bgm_exp_grp_name, t.bgm_exp_code"
                        + " order by t.bgm_strat_list_code, t.bgm_exp_grp_seq, t.bgm_exp_code";
                }
                else
                {
                    strSQL = "select t.bgm_strat_list_code, t.bgm_strat_list_name"
                        + ", t.bgm_exp_seq, t.bgm_exp_code, t.bgm_exp_name"
                        + ", t.bgm_exp_grp_seq, t.bgm_exp_grp_name"
                        + ", sum(coalesce(t.total_amount, 0)) as total_amount"

                        + " from ("
                            + " select sdl.bgm_strat_list_code, sdl.bgm_strat_list_name"
                            + ", e.bgm_exp_seq, e.bgm_exp_code"
                            + ", concat(ge.bgm_exp_grp_name, '/', e.bgm_exp_name) as bgm_exp_name"//, '/', e1.bgm_exp_name) as bgm_exp_name"
                            + ", e.bgm_exp_grp_seq, ge.bgm_exp_grp_name"
                            + ", re.total_amount"
                            + " from bgm_request r"
                            + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                            + " inner join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                            //+ " inner join bgm_mas_expense e on e1.upper_bgm_exp_seq = e.bgm_exp_seq"
                            + " inner join bgm_mas_expense_group ge on e.bgm_exp_grp_seq = ge.bgm_exp_grp_seq"
                            + " inner join bgm_strategy_data_list sdl on r.bgm_strat_list_seq = sdl.bgm_strat_list_seq"
                            + " where 0 = 0"
                                + (strRecordStatus.Length > 0 ?
                                    (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                                    : " and r.record_status not like 'D'")
                            + " and r.bgm_year = " + ddlYear.SelectedValue
                            + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                            + (cbSource.SelectedValue.Length > 0 ? " and re.bgm_source_seq = " + cbSource.SelectedValue : "")
                        //+ " and re.bgm_source_seq = " + cbSource.SelectedValue
                        + ") t"
                        + " group by t.bgm_strat_list_code, t.bgm_strat_list_name,t.bgm_exp_grp_name"
                            + ", t.bgm_exp_seq, t.bgm_exp_code, t.bgm_exp_name, t.bgm_exp_grp_seq"
                             + ", t.bgm_exp_grp_name, t.bgm_exp_code"
                        + " order by t.bgm_strat_list_code, t.bgm_exp_grp_seq, t.bgm_exp_code";
                }
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                string strOldBgmStratListCode = "";
                string strOldBgmExpGrpName = "";
                string strBgmStratListCode = "";
                string strBgmExpGrpName = "";
                string strBgmStratListName = "";
                string strBgmExpName = "";
                double dSumGrpAmount = 0;
                double dTotalAmount = 0;
                double dGrandTotalAmount = 0;
                int j = -1;
                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    strBgmStratListCode = dtb.Rows[i]["bgm_strat_list_code"].ToString();
                    dTotalAmount = Convert.ToDouble(dtb.Rows[i]["total_amount"]);
                    dGrandTotalAmount += dTotalAmount;
                    strBgmExpName = dtb.Rows[i]["bgm_exp_name"].ToString();
                    strBgmExpGrpName = dtb.Rows[i]["bgm_exp_grp_name"].ToString();

                    if (strOldBgmStratListCode != strBgmStratListCode)
                    {
                        strOldBgmStratListCode = strBgmStratListCode;
                        strBgmStratListName = dtb.Rows[i]["bgm_strat_list_name"].ToString();
                        ResultDTB.Rows.Add(strBgmStratListName);

                        if (j >= 0)
                        {
                            ResultDTB.Rows[j][strOldBgmExpGrpName] = dSumGrpAmount.ToString("#,###");
                            ResultDTB.Rows[j][ResultDTB.Columns.Count - 1] = dGrandTotalAmount.ToString("#,###");
                        }
                            
                        dSumGrpAmount = dTotalAmount;
                        dGrandTotalAmount = dTotalAmount;
                        strOldBgmExpGrpName = strBgmExpGrpName;
                        j++;
                    }
                    else
                    {
                        if (strOldBgmExpGrpName != strBgmExpGrpName || i == dtb.Rows.Count - 1)
                        {
                            if (i == dtb.Rows.Count - 1)
                            {
                                if (strOldBgmExpGrpName != strBgmExpGrpName)
                                {
                                    ResultDTB.Rows[j][strOldBgmExpGrpName] = dSumGrpAmount.ToString("#,###");
                                    ResultDTB.Rows[j][strBgmExpGrpName] = dTotalAmount.ToString("#,###");
                                }
                                else
                                {
                                    dSumGrpAmount = dSumGrpAmount + dTotalAmount;
                                    ResultDTB.Rows[j][strBgmExpGrpName] = dSumGrpAmount.ToString("#,###");
                                }
                            }
                            else
                            {
                                ResultDTB.Rows[j][strOldBgmExpGrpName] = dSumGrpAmount.ToString("#,###");
                                dSumGrpAmount = dTotalAmount;
                                strOldBgmExpGrpName = strBgmExpGrpName;
                            }
                        }
                        else
                        {
                            dSumGrpAmount = dSumGrpAmount + dTotalAmount;
                        }

                        dGrandTotalAmount += dTotalAmount;
                        ResultDTB.Rows[j][ResultDTB.Columns.Count - 1] = dGrandTotalAmount.ToString("#,###");
                    }

                    ResultDTB.Rows[j][strBgmExpName] = dTotalAmount.ToString("#,###");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }

            Excel_FromDataTable(ResultDTB);
        }

        protected void btnExportExcel2_Click(object sender, EventArgs e)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            DataTable ResultDTB = new DataTable();
            DataTable dtb;
            string strRecordStatus = ddlRecordStatus.SelectedValue;
            string strOldBgmExpGrpSeq = "";
            string strOldBgmExpName = "";

            if (strRecordStatus == "W")
            {
                strRecordStatus = "";
            }

            dbManager.Open();

            try
            {
                string strSQL = "";
                if (rblExpenseLevel.SelectedValue == "2")
                {
                    strSQL = "select distinct e2.bgm_exp_seq, e2.bgm_exp_code"
                        + ", concat(ge.bgm_exp_grp_name, '/', e2.bgm_exp_name) as bgm_exp_name"//, '/', e1.bgm_exp_name) as bgm_exp_name"
                        + ", e2.bgm_exp_grp_seq, ge.bgm_exp_grp_name"
                        + " from bgm_request r"
                        + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                        + (cbSource.SelectedValue.Length > 0 ? " and re.bgm_source_seq = " + cbSource.SelectedValue : "")
                        + " inner join bgm_mas_expense e1 on re.bgm_exp_seq = e1.bgm_exp_seq"// and e1.record_status not like 'D'"
                        + " inner join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"// and e2.record_status not like 'D'"
                        + " inner join bgm_mas_expense_group ge on e2.bgm_exp_grp_seq = ge.bgm_exp_grp_seq"

                        + " where 0=0"
                        + (strRecordStatus.Length > 0 ?
                                (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                                : " and r.record_status not like 'D'")
                            + " and r.bgm_year = " + ddlYear.SelectedValue
                            + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                        + " order by e2.bgm_exp_grp_seq, e2.bgm_exp_code";
                }
                else
                {
                    strSQL = "select distinct e2.bgm_exp_seq, e2.bgm_exp_code"
                        + ", concat(ge.bgm_exp_grp_name, '/', e2.bgm_exp_name) as bgm_exp_name"//, '/', e1.bgm_exp_name) as bgm_exp_name"
                        + ", e2.bgm_exp_grp_seq, ge.bgm_exp_grp_name"
                        + " from bgm_request r"
                        + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                        + (cbSource.SelectedValue.Length > 0 ? " and re.bgm_source_seq = " + cbSource.SelectedValue : "")
                        + " inner join bgm_mas_expense e2 on re.bgm_exp_seq = e2.bgm_exp_seq"// and e1.record_status not like 'D'"
                        //+ " inner join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"// and e2.record_status not like 'D'"
                        + " inner join bgm_mas_expense_group ge on e2.bgm_exp_grp_seq = ge.bgm_exp_grp_seq"

                        + " where 0=0"
                        + (strRecordStatus.Length > 0 ?
                                (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                                : " and r.record_status not like 'D'")
                            + " and r.bgm_year = " + ddlYear.SelectedValue
                            + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                        + " order by e2.bgm_exp_grp_seq, e2.bgm_exp_code";
                }
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                ResultDTB.Columns.Add("กิจกรรมย่อย");
                ResultDTB.Columns.Add("ชื่อโครงการ");
                ResultDTB.Columns.Add("สำนัก");

                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    string strBgmExpGrpSeq = dtb.Rows[i]["bgm_exp_grp_seq"].ToString();
                    string strBgmExpName1 = dtb.Rows[i]["bgm_exp_name"].ToString();

                    if (strOldBgmExpGrpSeq != strBgmExpGrpSeq)
                    {
                        ResultDTB.Columns.Add(dtb.Rows[i]["bgm_exp_grp_name"].ToString());
                        strOldBgmExpGrpSeq = strBgmExpGrpSeq;
                    }

                    if (strOldBgmExpName != strBgmExpName1)
                    {
                        ResultDTB.Columns.Add(strBgmExpName1);
                        strOldBgmExpName = strBgmExpName1;
                    }
                }

                ResultDTB.Columns.Add("รวมทั้งหมด");

                if (rblExpenseLevel.SelectedValue == "2")
                {
                    strSQL = "select t.bgm_strat_list_code, t.bgm_strat_list_name"
                        + ", t.job_site_name, t.bgm_req_name, t.bgm_exp_name"
                        + ", t.bgm_exp_grp_seq, t.bgm_exp_grp_name"
                        + ", sum(coalesce(t.total_amount, 0)) as total_amount, t.bgm_req_seq"

                        + " from ("
                            + " select sdl.bgm_strat_list_code, sdl.bgm_strat_list_name"
                            + ", e.bgm_exp_seq, e.bgm_exp_code"
                            + ", concat(ge.bgm_exp_grp_name, '/', e.bgm_exp_name) as bgm_exp_name"//, '/', e1.bgm_exp_name) as bgm_exp_name"
                            + ", e1.bgm_exp_grp_seq, ge.bgm_exp_grp_name"
                            + ", j.job_site_name, r.bgm_req_name"
                            + ", re.total_amount, r.bgm_req_seq"
                            + " from bgm_request r"
                            + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                            + " inner join bgm_mas_expense e1 on re.bgm_exp_seq = e1.bgm_exp_seq"
                            + " inner join bgm_mas_expense e on e1.upper_bgm_exp_seq = e.bgm_exp_seq"
                            + " inner join bgm_mas_expense_group ge on e1.bgm_exp_grp_seq = ge.bgm_exp_grp_seq"
                            + " inner join bgm_strategy_data_list sdl on r.bgm_strat_list_seq = sdl.bgm_strat_list_seq"
                            + " inner join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                        //+ " where r.record_status = '" + ddlRecordStatus.SelectedValue + "'"
                            + " where 0 = 0"
                                + (strRecordStatus.Length > 0 ?
                                    (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                                    : " and r.record_status not like 'D'")
                            + " and r.bgm_year = " + ddlYear.SelectedValue
                            + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                            + (cbSource.SelectedValue.Length > 0 ? " and re.bgm_source_seq = " + cbSource.SelectedValue : "")
                        + ") t"
                        + " group by t.bgm_strat_list_code, t.bgm_strat_list_name, t.bgm_exp_grp_name, t.bgm_exp_code"
                            + ", t.job_site_name, t.bgm_req_name, t.bgm_exp_name, t.bgm_exp_grp_seq, t.bgm_req_seq"
                             + ", t.bgm_exp_grp_name, t.bgm_exp_code"
                        + " order by t.bgm_strat_list_code, t.job_site_name, t.bgm_req_name, t.bgm_exp_code";
                }
                else
                {
                    strSQL = "select t.bgm_strat_list_code, t.bgm_strat_list_name"
                        + ", t.job_site_name, t.bgm_req_name, t.bgm_exp_name"
                        + ", t.bgm_exp_grp_seq, t.bgm_exp_grp_name"
                        + ", sum(coalesce(t.total_amount, 0)) as total_amount, t.bgm_req_seq"

                        + " from ("
                            + " select sdl.bgm_strat_list_code, sdl.bgm_strat_list_name"
                            + ", e.bgm_exp_seq, e.bgm_exp_code"
                            + ", concat(ge.bgm_exp_grp_name, '/', e.bgm_exp_name) as bgm_exp_name"//, '/', e1.bgm_exp_name) as bgm_exp_name"
                            + ", e.bgm_exp_grp_seq, ge.bgm_exp_grp_name"
                            + ", j.job_site_name, r.bgm_req_name"
                            + ", re.total_amount, r.bgm_req_seq"
                            + " from bgm_request r"
                            + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                            + " inner join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                            //+ " inner join bgm_mas_expense e on e1.upper_bgm_exp_seq = e.bgm_exp_seq"
                            + " inner join bgm_mas_expense_group ge on e.bgm_exp_grp_seq = ge.bgm_exp_grp_seq"
                            + " inner join bgm_strategy_data_list sdl on r.bgm_strat_list_seq = sdl.bgm_strat_list_seq"
                            + " inner join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                        //+ " where r.record_status = '" + ddlRecordStatus.SelectedValue + "'"
                            + " where 0 = 0"
                                + (strRecordStatus.Length > 0 ?
                                    (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                                    : " and r.record_status not like 'D'")
                            + " and r.bgm_year = " + ddlYear.SelectedValue
                            + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                            + (cbSource.SelectedValue.Length > 0 ? " and re.bgm_source_seq = " + cbSource.SelectedValue : "")
                        + ") t"
                        + " group by t.bgm_strat_list_code, t.bgm_strat_list_name,t.bgm_exp_grp_name,t.bgm_exp_code"
                            + ", t.job_site_name, t.bgm_req_name, t.bgm_exp_name, t.bgm_exp_grp_seq, t.bgm_req_seq"
                             + ", t.bgm_exp_grp_name, t.bgm_exp_code"
                        + " order by t.bgm_strat_list_code, t.job_site_name, t.bgm_req_name, t.bgm_exp_code";
                }
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                string strOldBgmReqSeq = "";
                string strOldBgmExpGrpName = "";
                string strBgmReqSeq = "";
                string strBgmReqName = "";
                string strBgmExpGrpName = "";
                string strBgmExpName = "";
                string strBgmStratListName = "";
                string strJobsiteName = "";
                double dSumGrpAmount = 0;
                double dTotalAmount = 0;
                double dGrandTotalAmount = 0;
                int j = -1;
                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    strBgmReqSeq = dtb.Rows[i]["bgm_req_seq"].ToString();
                    dTotalAmount = Convert.ToDouble(dtb.Rows[i]["total_amount"]);
                    strBgmExpName = dtb.Rows[i]["bgm_exp_name"].ToString();
                    strBgmExpGrpName = dtb.Rows[i]["bgm_exp_grp_name"].ToString();

                    if (strOldBgmReqSeq != strBgmReqSeq)
                    {
                        strOldBgmReqSeq = strBgmReqSeq;
                        strBgmReqName = dtb.Rows[i]["bgm_req_name"].ToString();
                        strBgmStratListName = dtb.Rows[i]["bgm_strat_list_name"].ToString();
                        strJobsiteName = dtb.Rows[i]["job_site_name"].ToString();
                        ResultDTB.Rows.Add(strBgmStratListName);

                        if (j >= 0)
                        {
                            ResultDTB.Rows[j][strOldBgmExpGrpName] = dSumGrpAmount.ToString("#,###");
                            ResultDTB.Rows[j][ResultDTB.Columns.Count - 1] = dGrandTotalAmount.ToString("#,###");
                        }

                        dSumGrpAmount = dTotalAmount;
                        dGrandTotalAmount = dTotalAmount;
                        strOldBgmExpGrpName = strBgmExpGrpName;
                        j++;
                        ResultDTB.Rows[j][1] = strBgmReqName;
                        ResultDTB.Rows[j][2] = strJobsiteName;
                    }
                    else
                    {
                        if (strOldBgmExpGrpName != strBgmExpGrpName || i == dtb.Rows.Count - 1)
                        {
                            if (i == dtb.Rows.Count - 1)
                            {
                                if (strOldBgmExpGrpName != strBgmExpGrpName)
                                {
                                    ResultDTB.Rows[j][strOldBgmExpGrpName] = dSumGrpAmount.ToString("#,###");
                                    ResultDTB.Rows[j][strBgmExpGrpName] = dTotalAmount.ToString("#,###");
                                }
                                else
                                {
                                    dSumGrpAmount = dSumGrpAmount + dTotalAmount;
                                    ResultDTB.Rows[j][strBgmExpGrpName] = dSumGrpAmount.ToString("#,###");
                                }
                            }
                            else
                            {
                                ResultDTB.Rows[j][strOldBgmExpGrpName] = dSumGrpAmount.ToString("#,###");
                                dSumGrpAmount = dTotalAmount;
                                strOldBgmExpGrpName = strBgmExpGrpName;
                            }
                        }
                        else
                        {
                            dSumGrpAmount = dSumGrpAmount + dTotalAmount;
                        }

                        dGrandTotalAmount += dTotalAmount;
                        ResultDTB.Rows[j][ResultDTB.Columns.Count - 1] = dGrandTotalAmount.ToString("#,###");
                    }
                    ResultDTB.Rows[j][strBgmExpName] = dTotalAmount.ToString("#,###");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }

            Excel_FromDataTable(ResultDTB);
        }

        private void Excel_FromDataTable(DataTable dtb)
        {
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("content-disposition", "attachment; filename=BudgetRequest-" + ddlYear.SelectedValue + ".xls");
            //Response.Charset = "";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);

            grdExcel.Visible = true;
            grdExcel.DataSource = dtb;
            grdExcel.DataBind();
            grdExcel.RenderControl(hw);

            Response.Write(tw.ToString());
            Response.End();
        }

        protected void btnBiRequest_Click(object sender, EventArgs e)
        {
        }
    }
}
